SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TMDOCU_PROV_Q11]      
/*-------------------------------------------------------*/      
/*---Empresa             : OFISIS S.A.                 --*/      
/*---Cliente             : MANUELA RAMOS               --*/      
/*---Sistema             : Presupuesto                 --*/      
/*---M½dulo              : Empresa                     --*/      
/*---Programa            : Pagos Proyectados por dias  --*/      
/*---      y semanas         --*/      
/*---Script              : TMDOCP_GENE_Q11.sql         --*/      
/*---Nombre SP           : SP_TMDOCP_GENE_Q11          --*/      
/*---Desarrollado por    : Merardo Antonio Rojas Mendoza-*/      
/*---Fecha Creaci½n      : 07/09/1999                  --*/      
/*---Base Datos          : Microsoft Sql Server        --*/      
/*---Versi½n             : 7.0                         --*/      
/*---Invoca a SP         :                             --*/      
/*-------------------------------------------------------*/      
      
/*-------------------------------------------------------*/      
/*---Modificado 1 por     : Daniel Chiang        --*/      
/*---Fecha Modificaci½n   : 07/04/2000         --*/       
/*---Detalle Modificaci½n :          --*/      
/*-------------------------------------------------------*/      
      
/*-------------------------------------------------------*/      
/*---Modificado 2 por     : Denis Villacr‚s        --*/      
/*---Fecha Modificaci½n   : 14/07/2000         --*/       
/*---Detalle Modificaci½n : Se Agreg¢ la variable.     --*/      
/*       @ISTI_FECH          --*/      
/*-------------------------------------------------------*/      
      
/*-------------------------------------------------------*/      
/*---Comentarios                                       --*/      
/*---                                                  --*/      
/*---                                                  --*/      
/*-------------------------------------------------------*/      
      
/*-------------------------------------------------------*/      
/* DROP PROC SP_TMDOCP_GENE_Q11     */      
/* GRANT ALL ON SP_TMDOCP_GENE_Q11 TO PUBLIC     */      
/* Execute SP_TMDOCU_PROV_Q11 '09/23/2004', 'DIA', 'V', 'D',  ""  */      
/*-------------------------------------------------------*/      
      
@IDFE_VENC  TD_DT_001,      
@ISTI_OPER  TD_VC_003,      
@ISTI_FECH  TD_VC_001,      
@ISTI_REPO TD_VC_001,      
@ISCA_WHER_LOCA VARCHAR(1000)      
  
  
 As      
DECLARE      
@VSCO_EMPR_INIC  TD_VC_002,      
@VSCO_EMPR_FINA  TD_VC_002,      
@VSCO_PROV_INIC  TD_VC_020,      
@VSCO_PROV_FINA  TD_VC_020,      
@VSCO_MONE_INIC  TD_VC_003,      
@VSCO_MONE_FINA  TD_VC_003,      
@VSDE_FECH  VarChar(30),      
@VSFE_VENC  VarChar(30)      
  
      
CREATE TABLE #TABLA(CO_EMPR CHAR(2),      
      CO_PROV VARCHAR(20),       
      CO_MONE CHAR(3),      
      CO_ESTA_DOCU VARCHAR(3),      
      DE_NOMB VARCHAR(100),      
      DE_MONE VARCHAR(50),      
      NO_AUXI VARCHAR(100),      
      CO_TIPO_DOCU VARCHAR(3),      
      NU_DOCU_PROV VARCHAR(20),      
      IM_0001 NUMERIC(16,4),      
      IM_0002 NUMERIC(16,4),      
      IM_0003 NUMERIC(16,4),      
      IM_0004 NUMERIC(16,4),      
      IM_0005 NUMERIC(16,4),      
      IM_0006 NUMERIC(16,4),      
      IM_0007 NUMERIC(16,4),      
      IM_0008 NUMERIC(16,4),      
      IM_0009 NUMERIC(16,4),      
      IM_0010 NUMERIC(16,4),      
      IM_0011 NUMERIC(16,4),      
      IM_0012 NUMERIC(16,4),      
      IM_0013 NUMERIC(16,4),      
      IM_0014 NUMERIC(16,4),      
      IM_0015 NUMERIC(16,4),      
      IM_SALD NUMERIC(16,4) DEFAULT 0)      
      
      
    
    
/* SELECCION POR FECHA DE VENCIMIENTO */      
      
IF @ISTI_FECH = 'V'       
SELECT @VSDE_FECH = 'TMDOCP_GENE.FE_VENC'      
      
IF @ISTI_FECH = 'P'       
SELECT @VSDE_FECH = 'TMDOCP_GENE.FE_PROG_PAGO'      
      
SELECT @VSFE_VENC = Convert(VarChar(10),@IDFE_VENC,103)      
      
      
IF @ISTI_REPO='D'      
BEGIN      
IF @ISTI_OPER = 'DIA'      
 BEGIN      
      
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI, CO_TIPO_DOCU, NU_DOCU_PROV,      
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,       
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,      
      
       IM_0013, IM_0014, IM_0015 )      
 SELECT       
  TMDOCP_GENE.CO_EMPR ,      
  TMDOCP_GENE.CO_MONE ,      
  TMDOCP_GENE.CO_PROV ,      
  TMDOCP_GENE.CO_ESTA_DOCU,      
  T1.DE_NOMB,       
  T2.DE_MONE,      
  T3.NO_AUXI,      
  TMDOCP_GENE.CO_TIPO_DOCU,      
 TMDOCP_GENE.NU_DOCU_PROV,      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))      
 FROM    TMDOCP_GENE  ,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3 , TTDOCU_CNTB T5      
 WHERE   1 = 1 '+@ISCA_WHER_LOCA+'      
And   '+@VSDE_FECH+'  >= Convert(DateTime,"'+@VSFE_VENC+'",103)      
   And   '+@VSDE_FECH+'  < Dateadd(day,16,Convert(DateTime,"'+@VSFE_VENC+'",103))      
   And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"      
   Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")      
   And  T1.CO_EMPR   = TMDOCP_GENE.CO_EMPR      
   And  TMDOCP_GENE.CO_MONE  = T2.CO_MONE      
   And  TMDOCP_GENE.CO_EMPR  = T3.CO_EMPR      
   And   T3.TI_AUXI_EMPR = "P"      
   And  T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV      
  AND  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU      
      
 ORDER BY  1 , 2, 3')      
      
END       
      
/*OPCION DE SEMANAS*/       
      
IF @ISTI_OPER = 'SEM'      
BEGIN       
      
      
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI, CO_TIPO_DOCU, NU_DOCU_PROV,      
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,       
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,      
       IM_0013, IM_0014, IM_0015 )      
 SELECT        
  TMDOCP_GENE.CO_EMPR ,      
  TMDOCP_GENE.CO_MONE ,      
  TMDOCP_GENE.CO_PROV ,      
  TMDOCP_GENE.CO_ESTA_DOCU,      
  T1.DE_NOMB,      
  T2.DE_MONE,      
  T3.NO_AUXI ,      
   TMDOCP_GENE.CO_TIPO_DOCU,      
               TMDOCP_GENE.NU_DOCU_PROV,      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),      
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))      
 From  TMDOCP_GENE  ,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3  ,  TTDOCU_CNTB T5      
 where  1 = 1 '+@ISCA_WHER_LOCA+'      
   And  '+@VSDE_FECH+' >= Convert(DateTime,"'+@VSFE_VENC+'",103)      
   And  '+@VSDE_FECH+' < dateadd(day,100,Convert(DateTime,"'+@VSFE_VENC+'",103))      
   And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"      
   Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")      
   And T1.CO_EMPR  =  TMDOCP_GENE.CO_EMPR      
   And TMDOCP_GENE.CO_MONE =  T2.CO_MONE      
   And TMDOCP_GENE.CO_EMPR =  T3.CO_EMPR      
   And    T3.TI_AUXI_EMPR = "P"      
   And T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV      
      
  AND  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU      
ORDER BY 1, 2, 3')      
      
END      
END      
      
      
ELSE      
BEGIN      
IF @ISTI_OPER = 'DIA'      
 BEGIN      
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI,      
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,       
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,      
       IM_0013, IM_0014, IM_0015 )      
 SELECT       
  TMDOCP_GENE.CO_EMPR ,      
  TMDOCP_GENE.CO_MONE ,      
  TMDOCP_GENE.CO_PROV ,      
  MAX(TMDOCP_GENE.CO_ESTA_DOCU),      
  MAX(T1.DE_NOMB),       
  MAX(T2.DE_MONE),      
  MAX(T3.NO_AUXI),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'  
+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) )      
 FROM    TMDOCP_GENE  ,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3 , TTDOCU_CNTB T5      
 WHERE   1 = 1 '+@ISCA_WHER_LOCA+'      
   And   '+@VSDE_FECH+'  >= Convert(DateTime,"'+@VSFE_VENC+'",103)      
   And   '+@VSDE_FECH+'  < Dateadd(day,16,Convert(DateTime,"'+@VSFE_VENC+'",103))      
   And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"      
   Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")      
   And  T1.CO_EMPR   = TMDOCP_GENE.CO_EMPR      
   And  TMDOCP_GENE.CO_MONE  = T2.CO_MONE      
   And  TMDOCP_GENE.CO_EMPR  = T3.CO_EMPR      
   And   T3.TI_AUXI_EMPR = "P"      
   And  T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV      
   AND  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU      
      
GROUP BY TMDOCP_GENE.CO_EMPR, TMDOCP_GENE.CO_MONE, TMDOCP_GENE.CO_PROV       
 ORDER BY  1 , 2, 3')      
      
END       
      
/*OPCION DE SEMANAS*/      
      
IF @ISTI_OPER = 'SEM'      
BEGIN       
      
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI,      
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,       
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,      
       IM_0013, IM_0014, IM_0015 )      
 SELECT        
  TMDOCP_GENE.CO_EMPR ,      
  TMDOCP_GENE.CO_MONE ,      
  TMDOCP_GENE.CO_PROV ,      
  MAX(TMDOCP_GENE.CO_ESTA_DOCU),      
  MAX(T1.DE_NOMB),      
  MAX(T2.DE_MONE),      
  MAX(T3.NO_AUXI) ,      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),      
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")))      
 From  TMDOCP_GENE  ,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3  ,  TTDOCU_CNTB T5      
 where  1 = 1 '+@ISCA_WHER_LOCA+'      
   And  '+@VSDE_FECH+' >= Convert(DateTime,"'+@VSFE_VENC+'",103)      
   And  '+@VSDE_FECH+' < dateadd(day,100,Convert(DateTime,"'+@VSFE_VENC+'",103))      
   And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"      
   Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")      
   And T1.CO_EMPR  =  TMDOCP_GENE.CO_EMPR      
   And TMDOCP_GENE.CO_MONE =  T2.CO_MONE      
   And TMDOCP_GENE.CO_EMPR =  T3.CO_EMPR      
   And    T3.TI_AUXI_EMPR = "P"      
   And T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV      
      
 AND  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU      
      
GROUP BY TMDOCP_GENE.CO_EMPR, TMDOCP_GENE.CO_MONE, TMDOCP_GENE.CO_PROV      
ORDER BY 1, 2, 3')      
      
      
END      
END      
      
  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI, CO_TIPO_DOCU, NU_DOCU_PROV,      
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,       
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,      
      
       IM_0013, IM_0014, IM_0015, IM_SALD )    
  
Select  TMDOCP_GENE.CO_EMPR ,      
  TMDOCP_GENE.CO_MONE ,      
  TMDOCP_GENE.CO_PROV ,      
  TMDOCP_GENE.CO_ESTA_DOCU,      
  T1.DE_NOMB,       
  T2.DE_MONE,      
  T3.NO_AUXI,      
  TMDOCP_GENE.CO_TIPO_DOCU,      
 TMDOCP_GENE.NU_DOCU_PROV,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, (IM_TOTA-IM_PAGA) *(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))      
From   TMDOCP_GENE , TMEMPR  T1, TTMONE T2, TMAUXI_EMPR T3, TTDOCU_CNTB T5      
Where   1 = 1 '+@ISCA_WHER_LOCA+'      
  And  TMDOCP_GENE.FE_VENC <  Convert(DateTime,"'+@VSFE_VENC+'",103)      
  And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"      
  Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")      
   And  T1.CO_EMPR   = TMDOCP_GENE.CO_EMPR      
   And  TMDOCP_GENE.CO_MONE  = T2.CO_MONE      
   And  TMDOCP_GENE.CO_EMPR  = T3.CO_EMPR      
   And   T3.TI_AUXI_EMPR = "P"      
   And  T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV  
   And  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU ')    
     
/*  
  
EXECUTE ('Insert into #TABLA (CO_EMPR,  CO_PROV , CO_MONE , CO_ESTA_DOCU ,      
           DE_NOMB , DE_MONE , NO_AUXI , IM_0001 ,IM_0002 ,      
                IM_0003 , IM_0004 , IM_0005 ,      
                IM_0006 , IM_0007 , IM_0008 ,      
                IM_0009 , IM_0010 , IM_0011 ,      
                IM_0012 , IM_0013 , IM_0014 ,      
                IM_0015 , IM_SALD)      
Select CO_EMPR, CO_PROV, CO_MONE, NULL, NULL, NULL, NULL, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, Sum(IM_TOTA-IM_PAGA)      
From   TMDOCP_GENE      
Where   1 = 1 '+@ISCA_WHER_LOCA+'      
  And  FE_VENC <  Convert(DateTime,"'+@VSFE_VENC+'",103)      
  And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"      
  Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")      
Group By     CO_EMPR, CO_MONE, CO_PROV')   
    
*/  
      
IF @ISTI_REPO='D'      
BEGIN      
SELECT CO_EMPR, CO_MONE, CO_PROV, MAX(CO_ESTA_DOCU), MAX(DE_NOMB),MAX(DE_MONE),      
  MAX(NO_AUXI),       
             SUM(ISNULL( IM_0001, 0) ), SUM(ISNULL(   
IM_0002, 0) ),      
 SUM(ISNULL( IM_0003, 0) ), SUM(ISNULL( IM_0004, 0) ),      
 SUM(ISNULL( IM_0005, 0) ), SUM(ISNULL( IM_0006, 0) ),      
 SUM(ISNULL( IM_0007, 0) ), SUM(ISNULL( IM_0008, 0) ),      
 SUM(ISNULL( IM_0009, 0) ), SUM(ISNULL( IM_0010, 0) ),      
 SUM(ISNULL( IM_0011, 0) ), SUM(ISNULL( IM_0012, 0) ),      
 SUM(ISNULL( IM_0013, 0) ), SUM(ISNULL( IM_0014, 0) ),      
 SUM(ISNULL( IM_0015, 0) ), SUM(ISNULL( IM_SALD, 0) ), CO_TIPO_DOCU, NU_DOCU_PROV      
FROM #TABLA       
GROUP BY CO_EMPR , CO_MONE, CO_PROV, CO_TIPO_DOCU, NU_DOCU_PROV      
HAVING (  SUM(ISNULL( IM_0001, 0) )<>0  OR    SUM(ISNULL( IM_0002, 0) )<>0   OR      
                 SUM(ISNULL( IM_0003, 0) )<>0   OR    SUM(ISNULL( IM_0004, 0) )<>0   OR      
     SUM(ISNULL( IM_0005, 0) )<>0   OR    SUM(ISNULL( IM_0006, 0) )<>0   OR      
     SUM(ISNULL( IM_0007, 0) )<>0   OR    SUM(ISNULL( IM_0008, 0) )<>0   OR      
     SUM(ISNULL( IM_0009, 0) )<>0   OR    SUM(ISNULL( IM_0010, 0) )<>0   OR  
     SUM(ISNULL( IM_SALD, 0) )<>0 )      
ORDER BY CO_EMPR , CO_MONE, CO_PROV, CO_TIPO_DOCU, NU_DOCU_PROV      
END      
      
ELSE      
BEGIN      
SELECT CO_EMPR, CO_MONE, CO_PROV, MAX(CO_ESTA_DOCU), MAX(DE_NOMB),MAX(DE_MONE),      
  MAX(NO_AUXI), SUM(ISNULL( IM_0001, 0) ), SUM(ISNULL( IM_0002, 0) ),      
 SUM(ISNULL( IM_0003, 0) ), SUM(ISNULL( IM_0004, 0) ),      
 SUM(ISNULL( IM_0005, 0) ), SUM(ISNULL( IM_0006, 0) ),      
 SUM(ISNULL( IM_0007, 0) ), SUM(ISNULL( IM_0008, 0) ),      
 SUM(ISNULL( IM_0009, 0) ), SUM(ISNULL( IM_0010, 0) ),      
 SUM(ISNULL( IM_0011, 0) ), SUM(ISNULL( IM_0012, 0) ),      
 SUM(ISNULL( IM_0013, 0) ), SUM(ISNULL( IM_0014, 0) ),      
 SUM(ISNULL( IM_0015, 0) ), SUM(ISNULL( IM_SALD, 0) ), NULL, NULL      
FROM #TABLA       
GROUP BY CO_EMPR , CO_MONE, CO_PROV      
HAVING (  SUM(ISNULL( IM_0001, 0) )<>0  OR    SUM(ISNULL( IM_0002, 0) )<>0   OR      
                 SUM(ISNULL( IM_0003, 0) )<>0   OR    SUM(ISNULL( IM_0004, 0) )<>0   OR      
     SUM(ISNULL( IM_0005, 0) )<>0   OR    SUM(ISNULL( IM_0006, 0) )<>0   OR      
     SUM(ISNULL( IM_0007, 0) )<>0   OR    SUM(ISNULL( IM_0008, 0) )<>0   OR      
     SUM(ISNULL( IM_0009, 0) )<>0   OR    SUM(ISNULL( IM_0010, 0) )<>0   OR      
     SUM(ISNULL( IM_SALD, 0) )<>0 )      
ORDER BY CO_EMPR , CO_MONE, CO_PROV      
END      
/*------------------------- FIN ------------------------------ */


GO
